package com.exmaple.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @author Create By Liuqz
 * @date 2018/7/25 12:19
 *
 * maven引入dependency
 *
 * <dependency>
 * <groupId>org.apache.poi</groupId>
 * <artifactId>poi</artifactId>
 * <version>3.17</version>
 * </dependency>
 */
public class ExcelDemo {
    private POIFSFileSystem fs;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private HSSFRow row;

    /**
     * 读取Excel表格表头的内容
     *
     * @param InputStream
     * @return String 表头内容的数组
     */
    public String[] readExcelTitle(InputStream is) {
        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            // title[i] = getStringCellValue(row.getCell((short) i));
            title[i] = getCellFormatValue(row.getCell((short) i));
        }
        return title;
    }

    /**
     * 读取Excel数据内容
     *
     * @param InputStream
     * @return Map 包含单元格数据内容的Map对象
     */
    public Map<Integer, String> readExcelContent(InputStream is) {
        Map<Integer, String> content = new HashMap<Integer, String>();
        String str = "";
        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            while (j < colNum) {
                // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
                // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
                // str += getStringCellValue(row.getCell((short) j)).trim() +
                // "-";
                str += getCellFormatValue(row.getCell((int) j)).trim() + "";
                j++;
            }
            content.put(i, str);
            str = "";
        }
        return content;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private String getStringCellValue(HSSFCell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }

    /**
     * 获取单元格数据内容为日期类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private String getDateCellValue(HSSFCell cell) {
        String result = "";
        try {
            int cellType = cell.getCellType();
            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
                Date date = cell.getDateCellValue();
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1) + "-" + date.getDate();
            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {
                String date = getStringCellValue(cell);
                result = date.replaceAll("[年月]", "-").replace("日", "").trim();
            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
                result = "";
            }
        } catch (Exception e) {
            System.out.println("日期格式不正确!");
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 根据HSSFCell类型设置数据
     *
     * @param cell
     * @return
     */
    private String getCellFormatValue(HSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式

                        // 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                        // cellvalue = cell.getDateCellValue().toLocaleString();

                        // 方法2：这样子的data格式是不带带时分秒的：2011-10-12
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue = sdf.format(date);

                    }
                    // 如果是纯数字
                    else {
                        // 取得当前Cell的数值
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                // 默认的Cell值
                default:
                    cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }

    public static boolean mkDirectory(String path) {
        File file = null;
        try {
            file = new File(path);
            if (!file.exists()) {
                return file.mkdirs();
            } else {
                return false;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            file = null;
        }
        return false;
    }

    public static void downLoadFromUrl(String urlStr, String fileName, String savePath) throws IOException {
        URL url = new URL(urlStr);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        // 设置超时间为3秒
        conn.setConnectTimeout(3 * 1000);
        // 防止屏蔽程序抓取而返回403错误
        conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");

        // 得到输入流
        InputStream inputStream = conn.getInputStream();
        // 获取自己数组
        byte[] getData = readInputStream(inputStream);

        // 文件保存位置
        File saveDir = new File(savePath);
        if (!saveDir.exists()) {
            saveDir.mkdir();
        }
        File file = new File(saveDir + File.separator + fileName);
        FileOutputStream fos = new FileOutputStream(file);
        fos.write(getData);
        if (fos != null) {
            fos.close();
        }
        if (inputStream != null) {
            inputStream.close();
        }

        System.out.println("info:" + url + " download success");

    }

    public static byte[] readInputStream(InputStream inputStream) throws IOException {
        byte[] buffer = new byte[1024];
        int len = 0;
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        while ((len = inputStream.read(buffer)) != -1) {
            bos.write(buffer, 0, len);
        }
        bos.close();
        return bos.toByteArray();
    }

    static boolean exists(String URLName) {

        try {

            // 设置此类是否应该自动执行 HTTP 重定向（响应代码为 3xx 的请求）。

            HttpURLConnection.setFollowRedirects(false);

            // 到 URL 所引用的远程对象的连接

            HttpURLConnection con = (HttpURLConnection) new URL(URLName)

                    .openConnection();

            /*
             * 设置 URL 请求的方法， GET POST HEAD OPTIONS PUT DELETE TRACE
             * 以上方法之一是合法的，具体取决于协议的限制。
             */

            con.setRequestMethod("HEAD");

            // 从 HTTP 响应消息获取状态码

            return (con.getResponseCode() == HttpURLConnection.HTTP_OK);

        } catch (Exception e) {

            e.printStackTrace();

            return false;

        }

    }

    public static void main(String[] args) throws IOException {
        try {
            // 对读取Excel表格标题测试
            ExcelDemo excelReader = new ExcelDemo();


            // 存储列名的xls文件
            InputStream is2 = new FileInputStream("C:\\work\\download\\data.xls");

            // 存储照片信息的xls文件
            InputStream download1 = new FileInputStream("C:\\work\\download\\download.xls");

            Map<Integer, String> map = excelReader.readExcelContent(is2);

            Map<Integer, String> downloadMap = excelReader.readExcelContent(download1);

            System.out.println("获得Excel表格的内容:");
            for (int i = 1; i <= map.size(); i++) {
                /**
                 * 在硬盘中创建文件夹,根据excel表中的列创建.
                 *
                 * 例: 列1
                 * 	  1.0 		则创建文件名为1.0的文件夹
                 *
                 * 列1 	列2
                 * 1.0  -XXX 	则创建文件名为1.0-XXX的文件夹
                 */

                // 在硬盘位置中创建文件夹的位置
                String mkDirectoryPath = "C:\\work\\download\\download\\download4\\" + map.get(i);
                // 创建文件夹
                if (mkDirectory(mkDirectoryPath)) {
                    System.out.println(mkDirectoryPath + "建立完毕");
                } else {
                    System.out.println(mkDirectoryPath + "建立失败!此目录已存在");
                }
                //开始下载
                for (int j = 1; j <= downloadMap.size(); j++) {
                    if (j == i) {
                        System.out.println("文件名:" + map.get(i) + "文件路径:" + downloadMap.get(j));
                        // 判断在服务器上的文件是否真实存在
                        if (exists("http://xxx.xx.x.x/pay/export/usrImg/" + downloadMap.get(j).trim())) {
                            /**
                             * 例:http://xxx.xx.x.x/pay/export/usrImg/xxxxxxxBACK.png
                             *
                             * http://xxx.xx.x.x/pay/export/usrImg/ 即为服务器地址
                             * C:\\work\\download\\download\\download4\\  即本机存储照片位置与创建文件夹时一致
                             *
                             * downLoadFromUrl(String urlStr, String fileName, String savePath)
                             */
                            downLoadFromUrl("http://xxx.xx.x.x/pay/export/usrImg/" + downloadMap.get(j).trim(),
                                    downloadMap.get(j).trim(),
                                    "C:\\work\\download\\download\\download4\\" + map.get(i).trim());
                        } else {
                            System.out.println("文件地址不存在");
                        }
                    }
                }
            }
        } catch (FileNotFoundException e) {
            System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        }
    }
}
